﻿using Dapper;
using HY.DataCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace HY.DataAchieve.Dapper
{
    /// <summary>
    /// Dapper隔离类
    /// </summary>
    internal class DapperHelper : IDapperHelper
    {
        /// <summary>
        /// Dapper 数据库上下文
        /// </summary>
        private DapperContext dapperContext = null;

        public DapperHelper(DapperContext dapperContext)
        {
            this.dapperContext = dapperContext;
        }

        /// <summary>
        /// 根据SQL查询列表
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL</param>
        /// <param name="param">参数</param>
        /// <param name="commandType">语句或存储过程</param>
        /// <param name="buffered">是否缓冲</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>查询结果泛型序列</returns>
        public IEnumerable<T> Query<T>(string sql, object param = null, CommandType commandType = CommandType.Text, bool buffered = true, int? commandTimeout = null)
        {
            dapperContext.TryOpenConnection();
            return dapperContext.dbConnecttion.Query<T>(sql, param, dapperContext.dbTransaction, buffered, commandTimeout, commandType);
        }

        /// <summary>
        /// 根据SQL查询实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL</param>
        /// <param name="param">参数</param>
        /// <param name="commandType">语句或存储过程</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public T QueryFirstOrDefault<T>(string sql, object param = null, CommandType commandType = CommandType.Text, int? commandTimeout = null)
        {
            dapperContext.TryOpenConnection();
            return dapperContext.dbConnecttion.QueryFirstOrDefault<T>(sql, param, dapperContext.dbTransaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="param">参数</param>
        /// <param name="commandType">语句或存储过程</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>受影响行数</returns>
        public int Execute(string sql, object param = null, CommandType commandType = CommandType.Text, int? commandTimeout = null)
        {
            dapperContext.TryOpenConnection();
            int result = dapperContext.dbConnecttion.Execute(sql, param, dapperContext.dbTransaction, commandTimeout, commandType);
            dapperContext.PersistenceLine += result;
            return result;
        }

        /// <summary>
        /// 查询取值
        /// </summary>
        /// <param name="sql">查询字符串</param>
        /// <param name="param">参数</param>
        /// <param name="commandType">语句或存储过程</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, object param = null, CommandType commandType = CommandType.Text, int? commandTimeout = null)
        {
            dapperContext.TryOpenConnection();
            return dapperContext.dbConnecttion.ExecuteScalar(sql, param, dapperContext.dbTransaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 查询取值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="sql">查询字符串</param>
        /// <param name="param">参数</param>
        /// <param name="commandType">语句或存储过程</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql, object param = null, CommandType commandType = CommandType.Text, int? commandTimeout = null)
        {
            dapperContext.TryOpenConnection();
            return dapperContext.dbConnecttion.ExecuteScalar<T>(sql, param, dapperContext.dbTransaction, commandTimeout, commandType);
        }

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="tableName">表</param>
        /// <param name="fields">列</param>
        /// <param name="where">条件</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public Tuple<int, IEnumerable<T>> GetPageList<T>(string tableName, string fields, string condition, int pageIndex, int pageSize, string orderBy, CommandType commandType = CommandType.Text, bool buffered = true, int? commandTimeout = null)
        {
            int skip = 1;
            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize + 1;
            }
            condition = string.IsNullOrWhiteSpace(condition) ? "" : "WHERE " + condition;
            dapperContext.TryOpenConnection();
            var totalCount= dapperContext.dbConnecttion.ExecuteScalar($"SELECT COUNT(1) FROM {tableName} {condition}", null, dapperContext.dbTransaction, commandTimeout, commandType);
            var selectSql = $"SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum,{fields} FROM {tableName} {condition}) AS result WHERE  RowNum >= {skip} AND RowNum <= {pageIndex * pageSize}";
            IEnumerable<T> data= dapperContext.dbConnecttion.Query<T>(selectSql, null, dapperContext.dbTransaction, buffered, commandTimeout, commandType);
            return new Tuple<int, IEnumerable<T>>((int)totalCount, data);
        }
    }
}
